import mysql.connector as mysqldb
import sys
from openpyxl import Workbook


# import pandas as pd
# def excel_export(table_name):
#     try:
#         conn = mysqldb.connect(host="sh-cdb-51zrhd4d.sql.tencentcdb.com", port=62874, user="root",
#                                passwd="huayuan@2018",
#                                db="kg_taiping", charset="utf8")
#         cursor = conn.cursor()
#         count = cursor.execute('select * from ' + table_name)
#         print(count)
#         results = cursor.fetchall()
#         # 获取MYSQL里面的数据字段名称
#         fields = cursor.description
#         wb = Workbook()
#         sheet = wb.active
#         for field in range(0, len(fields)):
#             sheet.write(0, field, fields[field][0])
#         row = 1
#         col = 0
#         for row in range(1, len(results) + 1):
#             for col in range(0, len(fields)):
#                 sheet.write(row, col, u'%s' % results[row - 1][col])
#         wb.save("sample.xlsx")
#     except Exception as e:
#         print(e)
#     return 0
#
#
# def export_datas(sql, filename='resp.xlsx'):
#     """
#     导出到本地
#     :param db:
#     :return:
#     """
#     rows = connect_datebase(sql)
#     dts = []
#     for row in rows:
#         try:
#             tp = {}
#             tp['ICD10'] = row['code_category']
#             tp['疾病名称'] = row['category_name']
#             tp['年份'] = row['y']
#             tp['地区'] = row['addr']
#             tp['年龄段'] = row['max_age']
#             tp['性别'] = row['gender']
#             tp['行业'] = row['hangye']
#             tp['职业'] = row['occup_code']
#             tp['发病人数'] = row['num_fabin']
#             tp['被保险人数'] = row['num_baoxian']
#             tp['发病率'] = float(row['fabinlv'])
#             dts.append(tp)
#         except Exception as e:
#             # log.error(e)
#             print(e)
#             continue
#     df = pd.DataFrame(dts)
#     df.to_excel(filename, sheet_name='发病情况')
#     print('数据导出完毕')


def connect_datebase(sql):
    try:
        conn = mysqldb.connect(host="sh-cdb-51zrhd4d.sql.tencentcdb.com", port=62874, user="root",
                               passwd="huayuan@2018",
                               db="kg_taiping", charset="utf8")
        cursor = conn.cursor()
        # sql = "SELECT * from diqu_fabin"
        n = cursor.execute(sql)
        rows = []
        col = []
        cols = cursor.description
        for c in cols:
            col.append(c[0])
        for em in cursor.fetchall():
            r = {}
            for i in range(0, len(col)):
                key = str(col[i])
                value = str(em[i])
                r[key] = value
            # row.append(r)
            rows.append(r)
    except Exception as e:
        print(e)
    return rows


def get_data(create_table_sql):
    try:
        print(create_table_sql)
        data = connect_datebase(create_table_sql)
    except mysqldb.connector.Error as err:
        print("create table 'mytable' failed.")
        print("Error: {}".format(err.msg))
        sys.exit()
    return data


# 生成默认4位长度随机字符串
import random


def create_4random_pwd(codelen=4):
    seed = "a b c d e f g h i j k l m n o p q r s t u v w x y z 0 1 2 3 4 5 6 7 8 9".split(' ')
    random.shuffle(seed)
    print(seed)
    return ''.join(seed[0:codelen])


if __name__ == '__main__':
    # conn = mysqldb.connect(host="sh-cdb-51zrhd4d.sql.tencentcdb.com", port=62874, user="root", passwd="huayuan@2018",
    #                        db="kg_taiping", charset="utf8")
    # cursor = conn.cursor()
    # sql = "SELECT * from diqu_fabin"
    # n = cursor.execute(sql)
    # for row in cursor.fetchall():
    #     print(row)
    # cursor.close()
    wb = Workbook()
    ws = wb.active
    ws['A1'] = 42
    ws.append([1, 2, 3])
    import datetime

    ws['A2'] = datetime.datetime.now()

    # Save the file
    wb.save("sample.xlsx")
